﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using TT.Module.sysLogin;
using System.Data;
using SfcorpDLL.ety;

namespace SfcorpDLL.dao
{
    public class daoSeachDtl
    {
        etyBaeSystemParamter basSysParam;
        public daoSeachDtl(etyBaeSystemParamter ieyBasClassDeclare)
        {
            basSysParam = ieyBasClassDeclare;
        }

        public DataTable GetAgyBnsResult(etyFrtAgyBns ey)
        {
            string sql = "select contract_no,PORT_NAM,CUSTOMER_NAM,FEE_WAY_NAM,FEE_WAY_NAM,I_E_NAM,COLLABORATE_NAM,WORK_WAY_NAM,CNTR_SIZ_COD,CNTR_TYP_COD "
                + ",SHIP_COD,VOYAGE,CARGO_NAM,UNDG_NO,FLOW_COD,CNTR,WEIGHING,BNS_SPC_MOD_DESC,FOREIGN_VVD_DESC"
                + ",CNTR_CORP_NAM,SEA_BILL_NO,CONTRACT_DATE,UPDATE_NAM,UPDATE_TIM"
                + " from VW_FRT_AGY_BNS "
                + " where ";
            sql += ey.SQL_WHERE;
            return basSysParam.SqlHelp.ExecuteReader(sql);
        }

        public DataTable GetAgyBnsSum(etyFrtAgyBns ey)
        {
//            //客户要求 45尺也是按2TEU计算
//            string sql = @"select contract_no,customer_id,customer_nam,REC_NAM
//                            ,port_id,port_nam,i_e_id,i_e_nam
//                            ,sea_bill_no,work_way_id,work_way_nam
//                            ,contract_date,wm_concat(cntr) cntr
//                            ,sum(decode(CNTR_SIZ_COD,'20',1,0)) as BOX20
//                            ,sum(decode(CNTR_SIZ_COD,'40',1,0)) as BOX40
//                            ,sum(decode(CNTR_SIZ_COD,'45',1,0)) as BOX45
//                            ,count(CNTR_SIZ_COD) as boxs 
//                            ,sum(decode(CNTR_SIZ_COD,'20',1,'40',2,'45',2,0)) as TEU
//                            ,sum(BR.I_AMOUNT) as I_AMOUNT
//                            ,sum(BR.O_AMOUNT) as O_AMOUNT
//                            ,FOREIGN_VVD_DESC
//                            ,max(UPDATE_NAM) UPDATE_NAM,disp_datetime(max(UPDATE_TIM)) UPDATE_TIM
//                            from VW_FRT_AGY_BNS b
//                            left join (select cd.bill_key,sum(DECODE(BC.IO_FLAG,1,cd.amount,0)) I_AMOUNT,sum(DECODE(BC.IO_FLAG,0,cd.amount,0)) O_AMOUNT from charge_dtl cd join bill_code bc on cd.item=bc.item GROUP BY cd.bill_key) br 
//                            on b.fabc_gkey=br.bill_key
//                            where bns_status<>'D' and ";
//            sql += ey.SQL_WHERE;
//            sql+= @" group by
//                    contract_no,customer_id,customer_nam,REC_NAM
//                    ,port_id,port_nam,i_e_id,i_e_nam,FOREIGN_VVD_DESC
//                    ,sea_bill_no,work_way_id,work_way_nam,contract_date";

            //,sea_bill_no,agent_bill_no

            string sql = @"select t.contract_no,t.customer_name,t.rec_nam,t.port_nam,t.i_e_nam
,CHRG_DESC5 work_way_nam,disp_datetime(contract_date) CONTRACT_DATE
,disp_datetime((max(UPDATE_TIM))) UPDATE_TIM,max(t.sea_bill_no) sea_bill_no
,max(UPDATE_NAM) update_nam,c.code_desc  FOREIGN_VVD_DESC
,sum(decode(CNTR_SIZ_COD,'20',1,0)) as BOX20
,sum(decode(CNTR_SIZ_COD,'40',1,0)) as BOX40
,sum(decode(CNTR_SIZ_COD,'45',1,0)) as BOX45
,sum(decode(CNTR_SIZ_COD,'20',1,'40',2,'45',2,0)) as TEU
,nvl(sum(decode(t.io_flag,1,t.amount)),0) I_AMOUNT
,nvl(sum(decode(t.io_flag,0,t.amount)),0) O_AMOUNT
,t.item_desc,st.code_desc bns_status_d ,bns_status_d bns_status 
from vw_frt_agy_bns_sum t
inner join code_ref c on c.item='FOREIGN_VVD' and c.code=t.foreign_vvd
left join code_ref st on t.bns_status_d=st.code and st.item='BNS_STAT' 
where 1=1 ";
            sql += string.IsNullOrEmpty(ey.SQL_WHERE) ? "" : " and " + ey.SQL_WHERE +"\r\n";
            sql += @"group by contract_no,t.customer_name,t.REC_NAM,t.port_id,port_nam,i_e_id,i_e_nam,c.code_desc,agent_bill_no,st.code_desc
,sea_bill_no,CHRG_DESC5,contract_date,t.item_desc ,bns_status_d 
order by contract_no desc ";
            return basSysParam.SqlHelp.ExecuteReader(sql);
        }
    }
}
